Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

Using MS SQL Server and DataServer options

This section provides information on using various ODBC and DataServer options.

Transaction Management: The AUTOCOMMIT option

To avoid using the MS SQL Server transaction manager, specify -Dsrv AUTOCOMMIT,1. This option exercises the OpenEdge client’s local before-image mechanism to manage transactions. This creates a transaction from each SQL statement that the DataServer issues. OpenEdge emulates the logical transaction on behalf of the application using the local before-image mechanism. This connection technique is useful in multi-database applications.

Note: OpenEdge does not guarantee crash recovery when it uses the local before-image mechanism to emulate transactions.

Query Result Order: the PRGRS_ALWAYS_INDEX option

Some queries can gain a performance advantage when the result set from the query does not use an index to order the results. In older versions of the DataServer for Microsoft SQL Server, results sets were always indexed because queries and their results were gathered in segments with new cursors being generated for each query segment. However, most advanced drivers and data sources now allow a single cursor to be retained for the life of a given query, even across a transaction boundary. Drivers that can preserve cursors are allowed to execute unindexed queries if the PRGRS_ALWAYS_INDEX option is set to 0 (off). When this option is off and the user does not specify an index or BY clause for the results of their 4GL statement and the particular 4GL being executed does not require scrolling capability in the OpenEdge client, the query result will remain unindexed by the DataServer.

Note: In an unindexed query, the DataServer will not send an ORDER BY clause to the data source. However, the DataServer has no control over whether or not the actual data source utilizes an index in order to generate a result set.

Concurrent query execution: The PRGRS_CACHE_CONN option

It is possible to run read-only queries in separate connections through the driver to the SQL Server. Opening a separate connection to run a query or stored procedure can provide better performance, although this is not guaranteed. Having too many open connections can degrade performance. The PRGRS_CACHE_CONN option allows you to set a limit for the maximum number of server connections available in the DataServer session. If the session attempts to exceed the maximum threshold, the session will need to wait for an existing connection to complete before an additional connection can be made.

Connection problems: The PRGRS_CONNECT option

The PRGRS_CONNECT option allows you to pass ODBC-specific information to the ODBC driver. This option has the following syntax:

Syntax

-Dsrv PRGRS_CONNECT,connection-string;

The connection string is separated from the option by a comma (,) and ends with a semicolon (;).

Use the PRGRS_CONNECT option in the following cases:

To connect to a data source whose name is not allowed by OpenEdge; for example, a name that includes blank spaces, ampersands (&), commas (,), and/or carets (^). In the connection string, pass the following characters rather than the unallowed characters. The driver resolves the passed characters to the unallowed character:
Pass the ampersand (&) character as two ampersand (&&) characters.
Pass the caret (^) character as two caret (^^) characters.
Pass the blank space character as an ampersand and a caret (&^).
Pass the comma (,) character as a caret and ampersand (^&).
To establish complex connections that require more than the Physical Database Name (-db), User ID (-U), and Password (-P) parameters. In all cases, the values must not be space delimited and must be passed in a single connection string.

For example, the following connection string sets the user ID and password for the server and user ID and password for the data source:

DSN=sports;UID=engine-login-name;PWD=engine-login-pass;UIDDBMS=dblogin; PWDDBMS=dblogin-pass

For more information and syntax examples, see the "Special connection issues" section.

Key-buffer size: The PRGRS_IDBUF option

The PRGRS_IDBUF option sets the size of the keys buffer. Generally, a default of 25 keys is sufficient. If the ODBC driver being used to access the SQL Server database has preserved cursors enabled across a transaction boundary, the keys buffer is used with all non-lookahead cursors. If the driver does have preserved cursors enabled, the PROGRS_IDBUF value and the keys buffer are unused.

Locking error messages: The PRGRS_LOCK_ERROR option

DataServer for MS SQL Server identifies and handles conditions and errors. However, the PRGRS_LOCK_ERROR option lets you control how your application reacts if it encounters an error that is actually a lock problem when accessing a data source. Use this option to pass the native error number to the DataServer so that it handles this error as it would an OpenEdge database lock problem; that is, the DataServer waits and retries, rather than halting the application:

CONNECT data-source-name -ld logical-name -dt mss -Dsrv PRGRS_LOCK_ERROR,error-number1,error-number2.

Large rows: The PRGRS_MINBUF option

Some data rows can be very large; for example, in an MS SQL Server database, rows often have large fields such as IMAGE and MEMO. The ODBC protocol specifies a dynamic buffer allocation process for handling large rows that do not initially fit into clients’ buffers; however, some drivers do not yet follow the correct ODBC protocol and do not handle these large rows correctly. Use the -Dsrv PRGRS_MINBUF,size option to force a minimum buffer size. For example, -Dsrv PRGRS_MINBUF,15000 enables the DataServer to handle 15K rows even with drivers that fail to follow the ODBC protocol.

The optimal setting for PRGRS_MINBUF is the size of the largest record data size plus 500 bytes. This can prevent run-time record expansion during the retrieval of query results.

Notes: Do not use this option when the -Dsrv BINDING switch is set to 3. With the binding set to 3, the size of the data is known, and this switch will cause the allocation of unneeded additional memory.

It is often difficult to determine when there is a buffer size problem and how to choose the correct value for PRGRS_MINBUF. Be careful when using this option.

SQL statements cache: The PRGRS_PREPCACHE option

The DataServer keeps a cache of prepared SQL statements that it reuses with new parameters. This mechanism improves the DataServer performance. You can use the PRGRS_PREPCACHE option to manage this cache in two ways:

The MS SQL Server drivers are capable of re-using prepared statements and should do so whenever possible. However, using -Dsrv PRGRS_PREPCACHE,0 instructs the DataServer to re-prepare each SQL statement.
Use the PRGRS_PREPCACHE option to control the size of the cache. The default cache size is 20 statements. You can increase the size for large applications that reuse many queries. The maximum size depends on the amount of resources you have available.

Concurrent procedure results: The PRGRS_PROC_TRAN option

The DataServer allows only one active request for running a stored procedure. However, you can process results form several stored procedures concurrently if you set the PRGRS_PROC_TRAN switch to 1 (ON.) When switched on, this option will cause a separate connection to be used for each stored procedure request, up to the maximum number of connections specified by the PRGRS_CACHE_CONN option.

Caution: When procedures run in separate connections of the same DataServer session, the scope of their respective transactions is isolated from one another. If one active procedure attempts to update the same record used by another active procedure in the same session, a lock timeout or even a deadlock could occur.

Cursor characteristics: The PRGRS_STABLE_CURS option

Enabling this switch indicates to the DataServer that it should assume that all cursors are stable. Normally, the ODBC driver and SQL Server determines whether a cursor is stable during the commit or rollback of a transaction and if the cursor can persist beyond a single transaction scope. The DataServer normally resolves cursor characteristics by interrogating the driver and setting the run-time environment accordingly.

Progress Software Corporation does not recommend bypassing normal operation under most circumstances, but under very limited circumstances, you can improve performance by overriding the derived cursor setting by setting the PRGRS_STABLE_CURS option to 1 (ON). Your application must comply to one of the following restrictions to safely enable this option:

All the 4GL run in your DataServer session is without transactions.
The 4GL run in your DataServer session has transactions, but all 4GL queries and resultant data sets are fully processed on one side of any existing transaction boundary.
Note: This is a session-level switch, which means that all 4GL run in the session must comply with the listed restrictions.

Wait time for asynchronous connections: the PRGRS_WAIT_DELAY option

The PRGRS_WAIT_DELAY switch allows you to determine the number of seconds you will allow the DataServer to delay further execution while waiting for the ODBC driver to respond to a request that may initiate a lock on a database resource. The delay is initiated when the NO-WAIT option is used in your 4GL and the DataServer is awaiting a response to a database request made through the ODBC driver to SQL Server. The PRGRS_WAIT_DELAY switch is ignored unless you are running in ODBC asynchronous mode. Check your driver and OpenEdge connection requirements to determine if you are allowed to run in asynchronous mode.

Preserve cursors: The -Dsrv MSS_PRESERVE_CURS,1 option

The -Dsrv MSS_PRESERVE_CURS,1 startup option allows server side cursors in Microsoft SQL Server to be preserved beyond a transaction boundary. Under many circumstances, preserved cursors can result in a significant performance improvement. Enabling this feature can result in enhanced performance, but performance gains are not guaranteed and in limited circumstances performance degrades. For this reason, this option is disabled by default. To test if preserve cursors benefit your application, enable this option by toggling the switch on with -Dsrv MSS_PRESERVE_CURS,1.

RECID Buffer size: The ZPRGRS_RECID_BUF_SIZE,nnn Option

The DataServer for MSS tries to select a unique index over each table defined in your schema holder to support the Progress RECID/ROWID functions. RECID functionality enables backward and forward scrolling in the DataServer product. The RECID buffer is used to sort key information about the unique index selected to support RECID. The default RECID buffer size is 245 bytes. The space is used dynamically to allocate the RECID buffer for a given record. The RECID buffer size needs to be large enough to contain all the key information regarding the unique index. If the unique index selected is a composite of many columns or contains large column names, the RECID key information might exceed the RECID buffer size and issue error message 2090. If you exceed the RECID buffer limit, Progress Software Corporation first recommends that you try to find an alternative unique index with a smaller number of key fields. This should help to improve performance during record access as well. You can change the RECID index in the Data Dictionary by selecting the DataServer button from the Table Properties of the table in your schema holder.

If it is not possible to change the selected RECID unique index for the table that is causing the buffer overflow, you can expand the area by setting the startup option as follows:

-Dsrv ZPRGRS_RECID_BUF_SIZE,nnn

Where nnn is the new size of the area in bytes.

You can expand the RECID buffer size to a maximum of 1000 bytes. The minimum size is 44 bytes. The algorithm to determine minimum adjusted size is as follows:

9 + Length of first Index column name + Length of first column data + 17
+ Length of second Index column name + Length of second column data + 22
+ Length of third Index column name + Length of third column data + 22
+ ...